package com.mbc.rmt.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import com.mbc.common.dao.DAO;
import com.mbc.rmt.entities.CashRemittanceByAgentDetails;

public class CashRemittanceByAgentArchiveDetailDAOImpls extends DAO<CashRemittanceByAgentDetails> implements CashRemittanceByAgentArchiveDetailDAO{

	public CashRemittanceByAgentArchiveDetailDAOImpls(Connection conn) {
		this.conn = conn;
		this.table_name = "CashRemittanceByAgentDetails_Archive";
	}

	@Override
	public void create(CashRemittanceByAgentDetails entity) throws SQLException {
		query = "insert into "
				+ table_name
				+ " ( ReceiptDetailID, ReceiptID, AddCodeID, Sender, Receiver, PaymentAmount, ReceivedAmount, ExtraFees, NRC, Address, ToAgentID, ToBranchID) values( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setLong(i++, entity.getReceiptDetailId());
		pstmt.setLong(i++, entity.getReceiptId());
		pstmt.setLong(i++, entity.getAddcodeId());
		pstmt.setString(i++, entity.getSender());
		pstmt.setString(i++, entity.getReceiver());
		pstmt.setDouble(i++, entity.getPaymentAmt());
		pstmt.setDouble(i++, entity.getReceivedAmt());
		pstmt.setDouble(i++, entity.getExtraFees());
		pstmt.setString(i++, entity.getNrc());
		pstmt.setString(i++, entity.getAddress());
		if(entity.getToAgentId()!=null)
			pstmt.setLong(i++, entity.getToAgentId());
		else
			pstmt.setString(i++, null);
		if(entity.getToBranchId()!=null)
			pstmt.setLong(i++, entity.getToBranchId());
		else
			pstmt.setString(i++, null);
		pstmt.executeUpdate();
		pstmt.close();
		
	}

	@Override
	public void update(CashRemittanceByAgentDetails entity) throws SQLException {
		query = "update "
				+ table_name
				+ " set ReceiptID = ?, AddCodeID = ?, Sender = ?, Receiver = ?, PaymentAmount = ?, ReceivedAmount = ?, ExtraFees = ?, NRC = ?, Address = ?, ToAgentID = ?, ToBranchID = ?  where ReceiptDetailID = "
				+ entity.getReceiptDetailId();
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setLong(i++, entity.getReceiptId());
		pstmt.setLong(i++, entity.getAddcodeId());
		pstmt.setString(i++, entity.getSender());
		pstmt.setString(i++, entity.getReceiver());
		pstmt.setDouble(i++, entity.getPaymentAmt());
		pstmt.setDouble(i++, entity.getReceivedAmt());
		pstmt.setDouble(i++, entity.getExtraFees());
		pstmt.setString(i++, entity.getNrc());
		pstmt.setString(i++, entity.getAddress());
		if(entity.getToAgentId()!=null)
			pstmt.setLong(i++, entity.getToAgentId());
		else
			pstmt.setString(i++, null);
		if(entity.getToBranchId()!=null)
			pstmt.setLong(i++, entity.getToBranchId());
		else
			pstmt.setString(i++, null);
		pstmt.executeUpdate();
		pstmt.close();
		
	}

	@Override
	public void delete(long key) throws SQLException {
		query = getDeleteQuery() + " where ReceiptDetailID = " + key;
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();
		
	}

	@Override
	public void deleteByReceiptId(long ReceiptId) throws SQLException {
		query = getDeleteQuery() + " where ReceiptID = " + ReceiptId;
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();
		
	}

	@Override
	public ArrayList<CashRemittanceByAgentDetails> findCRByAgentDetails(
			long ReceiptId) throws SQLException {
		ArrayList<CashRemittanceByAgentDetails> l_list = new ArrayList<CashRemittanceByAgentDetails>();

		query = " select crad.ReceiptDetailID,crad.ReceiptID, " +
				" crad.AddCodeID,ac.Code,crad.Sender,crad.Receiver, " +
				" crad.PaymentAmount,crad.ReceivedAmount,cra.Rate,crad.ExtraFees, " +
				" crad.NRC,crad.Address,crad.ToAgentID,isnull(a.agent_code,'')ToAgentCode,isnull(a.name,'')ToAgentName, " +
				" crad.ToBranchID,isnull(b.BranchCode,'')ToBranchCode, isnull(b.Name,'')ToBranchName " +
				" from CashRemittanceByAgentDetails crad " +
				" left join CashRemittanceByAgent cra on cra.ReceiptID = crad.ReceiptID " +
				" left join AddCode ac on ac.AddCodeId = crad.AddCodeId " +
				" left join Agent a on a.agent_id=crad.ToAgentID " +
				" left join Branch b on b.BranchId=crad.ToBranchID " +
				" where crad.ReceiptID = " + ReceiptId ; 
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			do {
				l_list.add(readRecords(rs));
			} while (rs.next());
		}
		rs.close();
		pstmt.close();
		return l_list;
	}

	private CashRemittanceByAgentDetails readRecords(ResultSet rs)throws SQLException {
		CashRemittanceByAgentDetails l_entity = new CashRemittanceByAgentDetails();
		l_entity.setReceiptDetailId(rs.getLong("ReceiptDetailID"));
		l_entity.setReceiptId(rs.getLong("ReceiptID"));
		l_entity.setAddcodeId(rs.getLong("AddCodeID"));
		l_entity.setCode(rs.getString("Code"));
		l_entity.setSender(rs.getString("Sender"));
		l_entity.setReceiver(rs.getString("Receiver"));
		l_entity.setPaymentAmt(rs.getDouble("PaymentAmount"));
		l_entity.setRate(rs.getDouble("Rate"));
		l_entity.setReceivedAmt(rs.getDouble("ReceivedAmount"));
		l_entity.setExtraFees(rs.getDouble("ExtraFees"));
		l_entity.setNrc(rs.getString("NRC"));
		l_entity.setAddress(rs.getString("Address"));
		l_entity.setToAgentId(rs.getLong("ToAgentID"));
		l_entity.setToAgentCode(rs.getString("ToAgentCode"));
		l_entity.setToAgentName(rs.getString("ToAgentName"));
		l_entity.setToBranchId(rs.getLong("ToBranchID"));
		l_entity.setToBranchCode(rs.getString("ToBranchCode"));
		l_entity.setToBranchName(rs.getString("ToBranchName"));
		return l_entity;
	}

	@Override
	protected CashRemittanceByAgentDetails readRecord(ResultSet rs)
			throws SQLException {
		CashRemittanceByAgentDetails l_entity = new CashRemittanceByAgentDetails();
		l_entity.setReceiptDetailId(rs.getLong("ReceiptDetailID"));
		l_entity.setReceiptId(rs.getLong("ReceiptID"));
		l_entity.setAddcodeId(rs.getLong("AddCodeID"));
		l_entity.setSender(rs.getString("Sender"));
		l_entity.setReceiver(rs.getString("Receiver"));
		l_entity.setPaymentAmt(rs.getDouble("PaymentAmount"));
		l_entity.setReceivedAmt(rs.getDouble("ReceivedAmount"));
		l_entity.setExtraFees(rs.getDouble("ExtraFees"));
		l_entity.setNrc(rs.getString("NRC"));
		l_entity.setAddress(rs.getString("Address"));
		l_entity.setToAgentId(rs.getLong("ToAgentID"));
		l_entity.setToBranchId(rs.getLong("ToBranchID"));
		return l_entity;
	}
	
	public ArrayList<CashRemittanceByAgentDetails> findCRByAgentDetailsArchive(
			long ReceiptId) throws SQLException {
		ArrayList<CashRemittanceByAgentDetails> l_list = new ArrayList<CashRemittanceByAgentDetails>();

		query = " select crad.ReceiptDetailID,crad.ReceiptID, " +
				" crad.AddCodeID,ac.Code,crad.Sender,crad.Receiver, " +
				" crad.PaymentAmount,crad.ReceivedAmount,cra.Rate,crad.ExtraFees, " +
				" crad.NRC,crad.Address,crad.ToAgentID,isnull(a.agent_code,'')ToAgentCode,isnull(a.name,'')ToAgentName, " +
				" crad.ToBranchID,isnull(b.BranchCode,'')ToBranchCode, isnull(b.Name,'')ToBranchName, cra.BankID, ba.BankCode " +
				" from CashRemittanceByAgentDetails_Archive crad " +
				" left join CashRemittanceByAgent_Archive cra on cra.ReceiptID = crad.ReceiptID " +
				" left join AddCode ac on ac.AddCodeId = crad.AddCodeId " +
				" left join Bank ba on  ba.BankId=cra.BankID " +
				" left join Agent a on a.agent_id=crad.ToAgentID " +
				" left join Branch b on b.BranchId=crad.ToBranchID " +
				" where crad.ReceiptID = " + ReceiptId +
				" and cra.status = 2 "; 
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			do {
				l_list.add(readRecordArchive(rs));
			} while (rs.next());
		}
		rs.close();
		pstmt.close();
		return l_list;
	}
	
	private CashRemittanceByAgentDetails readRecordArchive(ResultSet rs)throws SQLException {
		CashRemittanceByAgentDetails l_entity = new CashRemittanceByAgentDetails();
		l_entity.setReceiptDetailId(rs.getLong("ReceiptDetailID"));
		l_entity.setReceiptId(rs.getLong("ReceiptID"));
		l_entity.setAddcodeId(rs.getLong("AddCodeID"));
		l_entity.setCode(rs.getString("Code"));
		l_entity.setSender(rs.getString("Sender"));
		l_entity.setReceiver(rs.getString("Receiver"));
		l_entity.setPaymentAmt(rs.getDouble("PaymentAmount"));
		l_entity.setRate(rs.getDouble("Rate"));
		l_entity.setReceivedAmt(rs.getDouble("ReceivedAmount"));
		l_entity.setExtraFees(rs.getDouble("ExtraFees"));
		l_entity.setNrc(rs.getString("NRC"));
		l_entity.setAddress(rs.getString("Address"));
		l_entity.setToAgentId(rs.getLong("ToAgentID"));
		l_entity.setToAgentCode(rs.getString("ToAgentCode"));
		l_entity.setToAgentName(rs.getString("ToAgentName"));
		l_entity.setToBranchId(rs.getLong("ToBranchID"));
		l_entity.setToBranchCode(rs.getString("ToBranchCode"));
		l_entity.setToBranchName(rs.getString("ToBranchName"));
		l_entity.setBankId(rs.getLong("BankId"));
		l_entity.setBankCode(rs.getString("BankCode"));
		return l_entity;
	}
}
